﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.IO;

namespace Mind.Data
{
    public class DataSourceSchema
    {
        public class Table
        {
            public string Name { get; set; }
            public Dictionary<string, Column> Columns { get; private set; }
            public Dictionary<string, Column> Index { get; private set; }

            public Table()
            {
                Columns = new Dictionary<string, Column>();
                Index = new Dictionary<string, Column>();
            }


        }

        public class Column
        {
            public string Name { get; set; }
            public string Type { get; set; }
            public bool AutoInc { get; set; }
            public bool PrimaryKey { get; set; }
            public Table Table { get; set; }
            public Column ForeyingColumn { get; set; }
            public bool AllowNull { get; set; }
            public string DefaultValue { get; set; }
            public List<Column> Childs { get; private set; }
            public string IndexName { get; set; }
            public bool Index { get; set; }
            public bool Unique { get; set; }
            public string DbType { get; set; }

            public Column()
            {
                Childs = new List<Column>();
            }
        }

        public List<Table> Tables { get; private set; }

        private string MapType(string dbType)
        {
            if (dbType.Contains("char") || dbType.Contains("text"))
                return "string";

            if (dbType.Contains("integer"))
                return "int";

            return dbType;

        }

        public static string SchemaDiscover(string dataAccessName)
        {
            StringBuilder result = new StringBuilder();
            DbCommand cmd = DataAccessManager.Connections[dataAccessName].CreateCommand("SELECT 1");
            DbConnection con = cmd.Connection;

            result.AppendLine(@"
<html>
<head>
<title>Schema Report</title>
    <style type=""text/css"">
        p
        {
            font-family: Verdana, Arial;
            font-size: 14px;
            font-weight: bold;
        }
        table
        {
            border-width: 1px;
            border-color: Black;
            border-style: solid;
        }
        th
        {
            font-family: Verdana, Arial;
            font-size: 12px;
            font-weight: bold;
        }
        tr
        {
            font-family: Verdana, Arial;
            font-size: 10px;
            font-weight: normal;
        }
        td
        {
            border-width: 1px;
            border-color: Black;
            border-style: solid;
        }

    </style> 
</head>
<body>
<p>Meta Dados:</p>");
            DataTable metadata = con.GetSchema();
            DrawData(result, metadata);

            foreach (DataRow row in metadata.Rows)
            {
                string tablename = row[0].ToString();
                try
                {
                    DataTable table = con.GetSchema(tablename);
                    result.AppendFormat("<p>{0}</p>", table.TableName);
                    result.AppendLine();
                    DrawData(result, table);
                }
                catch(Exception ex)
                {
                    result.AppendFormat("<p>Error getting {0} data: <br> {1}</p>", tablename, ex);
                    result.AppendLine();
                }
            }

            result.AppendLine("</body></html>");
            return result.ToString();
        }

        private static void DrawData(StringBuilder result, DataTable table)
        {
            result.AppendLine("<table>");
            result.AppendLine("<tr>");
            foreach (DataColumn col in table.Columns)
            {
                result.AppendLine("<th>");
                result.AppendLine(col.ColumnName);
                result.AppendLine("</th>");
            }
            result.AppendLine("</tr>");

            foreach (DataRow row in table.Rows)
            {
                result.AppendLine("<tr>");

                foreach (object item in row.ItemArray)
                {
                    result.AppendLine("<td>");
                    result.AppendLine(item != null ? item.ToString() : "null");
                    result.AppendLine("</td>");
                }

                result.AppendLine("</tr>");
            }

            result.AppendLine("</table><hr>");
        }


        public DataSourceSchema(string dataAccessName)
        {
            Dictionary<string, Table> tables = new Dictionary<string, Table>();
            Dictionary<string, string> types = new Dictionary<string, string>();

            DbCommand cmd = DataAccessManager.Connections[dataAccessName].CreateCommand("SELECT 1");
            DbConnection con = cmd.Connection;

            DataTable typesSchema = con.GetSchema("DataTypes");
            foreach (DataRow dr in typesSchema.Rows)
            {
                string typeName = dr[0].ToString().ToLowerInvariant();
                if (!types.ContainsKey(typeName))
                    types[typeName] = dr[5].ToString().Replace("System.", "");
            }
            
            DataTable tableSchema = con.GetSchema("Tables");
          
            foreach (DataRow dr in tableSchema.Rows)
            {
                if (DataAccessManager.Connections[dataAccessName].Provider == DataAccessProvider.SQLite 
                    && dr["TABLE_TYPE"].ToString() != "table") continue;
                Table table = new Table()
                {
                    Name = dr["TABLE_NAME"].ToString()
                };
                if (!tables.ContainsKey(table.Name) && table.Name != "sysdiagrams")
                    tables.Add(table.Name, table);
            }

            DataTable columnSchema = con.GetSchema("Columns");
            Dictionary<string, bool> sqlServerPrimaryKeys = null;
            foreach (DataRow dr in columnSchema.Rows)
            {
                string tableName = dr["TABLE_NAME"].ToString();
                if (tables.ContainsKey(tableName))
                {
                    Table table = tables[tableName];
                    string columnName = dr["COLUMN_NAME"].ToString();
                    if (!table.Columns.ContainsKey(columnName))
                    {
                        if (DataAccessManager.Connections[dataAccessName].Provider == DataAccessProvider.SQLServer)
                        {
                            if (sqlServerPrimaryKeys == null)
                                sqlServerPrimaryKeys = BuscarPrimaryKeys(dataAccessName);
                            string key = table.Name + "_" + columnName;
                            
                            if (!sqlServerPrimaryKeys.ContainsKey(key))
                                sqlServerPrimaryKeys[key] = false;

                            table.Columns.Add(columnName, new Column()
                            {
                                AllowNull = (dr["IS_NULLABLE"] as bool?).GetValueOrDefault(),
                                AutoInc = false,
                                DefaultValue = (dr["COLUMN_DEFAULT"] is DBNull ? String.Empty : dr["COLUMN_DEFAULT"].ToString()),
                                Name = columnName,
                                PrimaryKey = sqlServerPrimaryKeys[key],
                                Table = table,
                                Type = types.ContainsKey(dr["DATA_TYPE"].ToString()) ? types[dr["DATA_TYPE"].ToString()] : MapType(dr["DATA_TYPE"].ToString()),
                                DbType = dr["DATA_TYPE"].ToString(),
                            });
                        }
                        else if (DataAccessManager.Connections[dataAccessName].Provider == DataAccessProvider.MySQL)
                        {
                            table.Columns.Add(columnName, new Column()
                            {
                                AllowNull = (dr["IS_NULLABLE"].ToString() != "NO"),
                                AutoInc = (dr["EXTRA"].ToString() == "auto_increment"),
                                DefaultValue = (dr["COLUMN_DEFAULT"] is DBNull ? String.Empty : dr["COLUMN_DEFAULT"].ToString().Replace("'","\"")),
                                Name = columnName,
                                PrimaryKey = (dr["COLUMN_KEY"].ToString() == "PRI"),
                                Table = table,
                                Type = types.ContainsKey(dr["DATA_TYPE"].ToString()) ? types[dr["DATA_TYPE"].ToString()] : MapType(dr["DATA_TYPE"].ToString()),
                                DbType = dr["DATA_TYPE"].ToString(),
                            });
                        }
                        else
                        {
                            table.Columns.Add(columnName, new Column()
                            {
                                AllowNull = (dr["IS_NULLABLE"] as bool?).GetValueOrDefault(),
                                AutoInc = false,
                                DefaultValue = (dr["COLUMN_DEFAULT"] is DBNull ? String.Empty : dr["COLUMN_DEFAULT"].ToString()),
                                Name = columnName,
                                PrimaryKey = (dr["PRIMARY_KEY"] as bool?).GetValueOrDefault(),
                                Table = table,
                                Type = types.ContainsKey(dr["EDM_TYPE"].ToString()) ? types[dr["EDM_TYPE"].ToString()] : MapType(dr["EDM_TYPE"].ToString()),
                                DbType = dr["EDM_TYPE"].ToString(),
                            });
                        }
                    }
                }
            }

            DataTable foreignSchema;
            if (DataAccessManager.Connections[dataAccessName].Provider == DataAccessProvider.SQLServer)
                foreignSchema = GetSqlServerForeignKeys(dataAccessName);
            else if (DataAccessManager.Connections[dataAccessName].Provider == DataAccessProvider.MySQL)
                foreignSchema = con.GetSchema("Foreign Key Columns");
            else
                foreignSchema = con.GetSchema("ForeignKeys");

            foreach (DataRow dr in foreignSchema.Rows)
            {
                string tableName = dr["TABLE_NAME"].ToString();
                string columnName;
                string toTableName;
                string toColumnName;

                if (DataAccessManager.Connections[dataAccessName].Provider == DataAccessProvider.MySQL)
                {
                    columnName = dr["COLUMN_NAME"].ToString();
                    toTableName = dr["REFERENCED_TABLE_NAME"].ToString();
                    toColumnName = dr["REFERENCED_COLUMN_NAME"].ToString();
                }
                else
                {
                    columnName = dr["FKEY_FROM_COLUMN"].ToString();
                    toTableName = dr["FKEY_TO_TABLE"].ToString();
                    toColumnName = dr["FKEY_TO_COLUMN"].ToString();
                }

                if (tables.ContainsKey(tableName) && tables.ContainsKey(toTableName))
                {
                    Table table = tables[tableName];
                    Table toTable = tables[toTableName];
                    if (table.Columns.ContainsKey(columnName) && toTable.Columns.ContainsKey(toColumnName))
                    {
                        Column col = table.Columns[columnName];
                        Column toCol = toTable.Columns[toColumnName];

                        col.ForeyingColumn = toCol;
                        toCol.Childs.Add(col);
                    }
                }
            }

            if (DataAccessManager.Connections[dataAccessName].Provider != DataAccessProvider.MySQL)
            {

                DataTable indexColumnSchema = con.GetSchema("IndexColumns");
                foreach (DataRow dr in indexColumnSchema.Rows)
                {
                    string tableName = dr["TABLE_NAME"].ToString();
                    string columnName = dr["COLUMN_NAME"].ToString();

                    if (tables.ContainsKey(tableName))
                    {
                        Table table = tables[tableName];

                        if (table.Columns.ContainsKey(columnName))
                        {
                            Column col = table.Columns[columnName];
                            col.IndexName = dr["INDEX_NAME"].ToString();
                            col.Index = true;
                            table.Index[col.IndexName] = col;
                        }
                    }
                }

                if (DataAccessManager.Connections[dataAccessName].Provider != DataAccessProvider.SQLServer)
                {

                    DataTable indexSchema = con.GetSchema("Indexes");
                    foreach (DataRow dr in indexSchema.Rows)
                    {
                        string tableName = dr["TABLE_NAME"].ToString();
                        string indexName = dr["INDEX_NAME"].ToString();

                        if (tables.ContainsKey(tableName))
                        {
                            Table table = tables[tableName];

                            if (table.Index.ContainsKey(indexName))
                            {
                                Column col = table.Index[indexName];
                                col.Unique = (dr["UNIQUE"] as bool?).GetValueOrDefault();
                                //TODO
                                col.AutoInc = (dr["PRIMARY_KEY"] as bool?).GetValueOrDefault();
                            }
                        }

                    }
                }

            }

            this.Tables = new List<Table>();
            foreach (Table item in tables.Values)
            {
                this.Tables.Add(item);
            }

            //DataTable schema = con.GetSchema("DataTypes");

            //StringBuilder sb = new StringBuilder();
            //int i = 0;
            //foreach (DataRow item in schema.Rows)
            //{
            //    foreach (DataColumn col in schema.Columns)
            //    {
            //        sb.AppendFormat("{0} - {1}: {2}", i++, col.ColumnName, item[col].ToString());
            //        sb.AppendLine();
            //    }
            //}

            //StringBuilder sb = new StringBuilder();
            //int i = 0;
            //foreach (DataColumn item in schema.Columns)
            //{
            //    sb.AppendFormat("{0}: {1}", i++, item.ColumnName);
            //    sb.AppendLine();
            //}

            //sb.AppendLine();
            //sb.AppendLine("***");
            //sb.AppendLine();
            //i = 0;
            //foreach (DataRow item in schema.Rows)
            //{
            //    sb.AppendFormat("{0}: {1} - {2}", i++, item[0].ToString(), item[5]);
            //    sb.AppendLine();
            //}
        }

        private Dictionary<string, bool> BuscarPrimaryKeys(string dataAccessName)
        {
            Dictionary<string, bool> result = new Dictionary<string,bool>();
            DataTable table = DataAccessManager.Connections[dataAccessName].GetDataTable(@"
SELECT
 T.TABLE_NAME,
 COALESCE( CU.CONSTRAINT_NAME , '(no primary key)') 
    AS PRIMARY_KEY_NAME,
 CU.COLUMN_NAME 
FROM INFORMATION_SCHEMA.TABLES AS T
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
 ON TC.TABLE_CATALOG = T.TABLE_CATALOG
 AND TC.TABLE_SCHEMA = T.TABLE_SCHEMA
 AND TC.TABLE_NAME = T.TABLE_NAME
 AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
 ON CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
 AND CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
 AND CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE
 T.TABLE_TYPE = 'BASE TABLE'
ORDER BY
 T.TABLE_NAME,
 CU.ORDINAL_POSITION

");

            foreach (DataRow dr in table.Rows)
            {
                string key = string.Format("{0}_{1}", dr["TABLE_NAME"].ToString(), dr["COLUMN_NAME"].ToString());
                result[key] = true;
            }

            return result;
        }

        private DataTable GetSqlServerForeignKeys(string dataAccessName)
        {
            return DataAccessManager.Connections[dataAccessName].GetDataTable(@"
SELECT 
    TABLE_NAME  = FK.TABLE_NAME, 
    FKEY_FROM_COLUMN = CU.COLUMN_NAME, 
    FKEY_TO_TABLE  = PK.TABLE_NAME, 
    FKEY_TO_COLUMN = PT.COLUMN_NAME, 
    Constraint_Name = C.CONSTRAINT_NAME 
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    INNER JOIN 
    ( 
        SELECT 
            i1.TABLE_NAME, i2.COLUMN_NAME 
        FROM 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
            INNER JOIN 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
            ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    ) PT 
    ON PT.TABLE_NAME = PK.TABLE_NAME 
-- optional: 
ORDER BY 
    1,2,3,4

");
        }

        public static string Capitalize(string text)
        {
            if (text.Length > 1)
                return string.Format("{0}{1}", text[0].ToString().ToUpper(), text.Remove(0, 1));
            else
                return text.ToUpper();
        }

        public void GenerateFiles(string outputDir, string namespaceName)
        {
            foreach (DataSourceSchema.Table table in this.Tables)
            {
                table.Name = Capitalize(table.Name);
                string filename = String.Format("{0}\\{1}.cs", outputDir, table.Name);
                string output;
                if (File.Exists(filename))
                {
                    string file = File.ReadAllText(filename);
                    output = WrapperGenerator.Generate(table, namespaceName, file);
                }
                else
                {
                    output = WrapperGenerator.Generate(table, namespaceName);
                }

                File.WriteAllText(filename, output);
            }
        }
    }
}
